package com.jdbcTemplate.service;

import com.jdbcTemplate.pojo.Actor;
import com.jdbcTemplate.pojo.RegionInfo;
import com.restTemplate.entity.Info;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @author CKC
 * @DateTime 2021/12/11 20:47
 */
@Service
@Transactional(readOnly = true)
@SuppressWarnings("all")
public class GetInfo {
    private final RowMapper<Actor> actorRowMapper = (resultSet, rowNum) -> {
        Actor actor = new Actor();
        actor.setFirstName(resultSet.getString("first_name"));
        actor.setLastName(resultSet.getString("last_name"));
        return actor;
    };
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private GetInfo getInfo;

    public List<RegionInfo> getRegionInfo() {

        String sqlQuery = "select province_name,city_name,county_name,town_name,village_name from 5ji";
        List<RegionInfo> list = new LinkedList<>();
        jdbcTemplate.query(sqlQuery, (resultSet) -> {
            RegionInfo regionInfo = new RegionInfo();
            regionInfo.setProviderName(resultSet.getString(1));
            regionInfo.setCityName(resultSet.getString(2));
            regionInfo.setCountryName(resultSet.getString(3));
            regionInfo.setTownName(resultSet.getString(4));
            regionInfo.setVillageName(resultSet.getString(5));
            list.add(regionInfo);
        });
        System.out.println(list.size());
        return list;
    }
    //------------------------------------

    public void test() {
        int rowCount = jdbcTemplate.queryForObject("select count(*) from 5ji", Integer.class);
        int countOfActorsNamedJoe = jdbcTemplate.queryForObject(
                "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

        //沒有表 SQL語句是偽造的 ,這些嗾使一些spring官網的demo
        Info actor1 = jdbcTemplate.queryForObject(
                "select first_name, last_name from t_actor where id = ?",
                (resultSet, rowNum) -> {
                    Info newActor = new Info();
                    newActor.setName(resultSet.getString("first_name"));
                    newActor.setAge(resultSet.getInt("last_name"));
                    return newActor;
                },
                1212L);

        List<Actor> actors = this.jdbcTemplate.query(
                "select first_name, last_name from t_actor",
                (resultSet, rowNum) -> {
                    Actor actor = new Actor();
                    actor.setFirstName(resultSet.getString("first_name"));
                    actor.setLastName(resultSet.getString("last_name"));
                    return actor;
                });


    }

    public List<Actor> findAllActors() {
        return this.jdbcTemplate.query("select first_name, last_name from t_actor", actorRowMapper);
    }


    public void test2() {
        //您可以使用该execute(..)方法运行任意 SQL
        jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

        //调用存储过程：
        jdbcTemplate.update(
                "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
                Long.valueOf("123456"));

    }

    public void test3() {
        final String INSERT_SQL = "insert into my_test (name) values(?)";
        final String name = "Rob";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        // i 是返回插入数据库后返回的主键，如果定义了主键自动递增
        int i = jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[]{"id"});
            ps.setString(1, name);
            return ps;
        }, keyHolder);
    }

    //批量操作
    public void batchUpdate(final List<Actor> actors) {
        int[] ints = jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Actor actor = actors.get(i);
                        ps.setString(1, actor.getFirstName());
                        ps.setString(2, actor.getLastName());
                        ps.setLong(3, actor.getId());
                    }

                    public int getBatchSize() {
                        return actors.size();
                    }
                });

    }

    //具有对象列表的批量操作
    public void batchUpdate1(List<Actor> actors) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Actor actor : actors) {
            Object[] values = new Object[]{
                    actor.getFirstName(), actor.getLastName(), actor.getId()};
            batch.add(values);
        }
        int[] ints = jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch); //如果计数不可用，则 JDBC 驱动程序返回值-2。

        //SqlParameterSource[] batch1 = SqlParameterSourceUtils.createBatch(actors);
        //jdbcTemplate.batchUpdate(
        //        "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
        //        batch1);
        //    spring.io  提供的一个demo
    }

    //多批次的批次操作
    public void batchUpdate2(List<Actor> actors) {
        int[][] updateCounts = jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                actors,
                100,
                (PreparedStatement ps, Actor actor) -> {
                    ps.setString(1, actor.getFirstName());
                    ps.setString(2, actor.getLastName());
                    ps.setLong(3, actor.getId().longValue());
                });
    }


}
